package com.progroup.kyrsmoney.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLData;

import android.app.DownloadManager.Query;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQuery;
import android.util.Log;

public class DBAdapter {
	
	
	
    public static String DB_NAME = "DataBase.v.1.1"; 
    private static int DB_VERSION = 1;
    private static DatabaseHelper DB_HELPER; 
    private static Context DB_CONTEXT;
    private SQLiteDatabase mDb;
    
    private static final String DB_TABLE_BANK = "tblBankKyrs";
    public static final String KEY_BANK_ID = "bank_id";
	public static final String KEY_BANK_USD_BUY = "usd_buy";
	public static final String KEY_BANK_USD_SALE = "usd_sale";
	public static final String KEY_BANK_EUR_BUY = "eur_buy";
	public static final String KEY_BANK_EUR_SALE = "eur_sale";
	public static final String KEY_BANK_RUB_BUY = "rub_buy";
	public static final String KEY_BANK_RUB_SALE = "rub_sale";
	public static final String KEY_BANK_DATE = "date";
    
    
    private static final String DB_TABLE_BANK_INFO = "tblBankInfo";
    public static final String KEY_BANK_INFO_ID = "_id";
	public static final String KEY_BANK_INFO_NAME = "name";
	public static final String KEY_BANK_INFO_URL = "url";
	public static final String KEY_BANK_INFO_TRUFALSE = "tru_fal";
   
   
	
	
	
	public static class DatabaseHelper extends SQLiteOpenHelper  
    {
		private Context context;
		 
		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			// TODO Auto-generated constructor stub
			this.context = context;
		}
		 public void createDataBase() throws IOException{
	            boolean dbExist = checkDataBase();
	     
	            if(!dbExist){ 
	                getReadableDatabase();
	     
	                try {
	                    copyDataBase();
	                } catch (IOException e) {}
	            }
	     
	        }
		 
		 private boolean checkDataBase(){
		     
	        	
	        		File DB_PATH = context.getDatabasePath(DB_NAME);
	        		if(DB_PATH.exists()){
	        			return true;
	        		}else{
	        			return false;
	        		}
	     
	        }
		 
		 private void copyDataBase() throws IOException{
	            InputStream myInput = context.getAssets().open(DB_NAME);
	            String outFileName = context.getDatabasePath(DB_NAME).toString();
	            OutputStream myOutput = new FileOutputStream(outFileName);
	            byte[] buffer = new byte[1024];
	        	int length;
	        	while ((length = myInput.read(buffer))>0){
	        		myOutput.write(buffer, 0, length);
	        	}
	        	myOutput.flush();
	        	myOutput.close();
	        	myInput.close();
	     
	        }
		 
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			  
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w("DBAdapter", "Upgrading database from version " + oldVersion + " to "  
                    + newVersion + ", which will destroy all old data");  
            db.execSQL("DROP TABLE IF EXISTS tblBankKyrs");  
            db.execSQL("DROP TABLE IF EXISTS tblBankInfo"); 
            onCreate(db); 
		}  
		
		
	     
    }
	
	
	 public DBAdapter(Context ctx) {  
	        DBAdapter.DB_CONTEXT = ctx;  
	    }  
	 
	 public DBAdapter open() throws SQLException {  
		 DB_HELPER = new DatabaseHelper(DB_CONTEXT);  
	        mDb = DB_HELPER.getWritableDatabase();  
	        return this;  
	    }  
	   
	    public void close() {  
	    	DB_HELPER.close();  

	    }
	    


	    
	    
	    
	    ///////////////////////////////////////////////////////
	    
	    public long DB_ADD_KYRS(int idBank, String USD_BUY, String USD_SALE, String EUR_BUY, String EUR_SALE, String RUB_BUY, String RUB_SALE, String date) {  
	    	ContentValues pInitialValues = new ContentValues();
	    	pInitialValues.put(KEY_BANK_ID, idBank);
	    	pInitialValues.put(KEY_BANK_USD_BUY, USD_BUY);
	    	pInitialValues.put(KEY_BANK_USD_SALE, USD_SALE);
	    	pInitialValues.put(KEY_BANK_EUR_BUY, EUR_BUY);
	    	pInitialValues.put(KEY_BANK_EUR_SALE, EUR_SALE);
	    	pInitialValues.put(KEY_BANK_RUB_BUY, RUB_BUY);
	    	pInitialValues.put(KEY_BANK_RUB_SALE, RUB_SALE);
	    	pInitialValues.put(KEY_BANK_DATE, date);
	    	//Log.i("DB_BANK_ADD", ""+pInitialValues.toString());
	    	return mDb.insert(DB_TABLE_BANK, null, pInitialValues);  
	    	
	    	
	    }
	    public long DB_UPDATE_KYRS_EUR(int idBank,String EUR_BUY, String EUR_SALE, String date) {  
	    	ContentValues pInitialValues = new ContentValues();
	    	pInitialValues.put(KEY_BANK_EUR_BUY, EUR_BUY);
	    	pInitialValues.put(KEY_BANK_EUR_SALE, EUR_SALE);
	    	//Log.i("DB_BANK_UPDATE", ""+pInitialValues.toString());
	    	return mDb.update(DB_TABLE_BANK, pInitialValues, KEY_BANK_ID+"='"+idBank+"' AND date='"+date+"'", null);  
	    	
	    	
	    }
	    public long DB_UPDATE_KYRS_USD(int idBank, String USD_BUY, String USD_SALE, String date) {  
	    	ContentValues pInitialValues = new ContentValues();
	    	pInitialValues.put(KEY_BANK_USD_BUY, USD_BUY);
	    	pInitialValues.put(KEY_BANK_USD_SALE, USD_SALE);
	    	//Log.i("DB_BANK_UPDATE", ""+pInitialValues.toString());
	    	return mDb.update(DB_TABLE_BANK, pInitialValues, KEY_BANK_ID+"='"+idBank+"' AND date='"+date+"'", null);  
	    	
	    	
	    }
	    public long DB_UPDATE_KYRS_RUB(int idBank, String RUB_BUY, String RUB_SALE, String date) {  
	    	ContentValues pInitialValues = new ContentValues();
	    	pInitialValues.put(KEY_BANK_RUB_BUY, RUB_BUY);
	    	pInitialValues.put(KEY_BANK_RUB_SALE, RUB_SALE);
	    	//Log.i("DB_BANK_UPDATE", ""+pInitialValues.toString());
	    	return mDb.update(DB_TABLE_BANK, pInitialValues, KEY_BANK_ID+"='"+idBank+"' AND date='"+date+"'", null);  
	    	
	    	
	    }
	    
	    public Cursor DB_GET_ID() {  //вытаскиваем все ID
	    	
	    	return mDb.rawQuery("SELECT _id FROM tblBankInfo", null);
	    }
	    
	    
	    public Cursor DB_GET_ID_TODAY(String todayDate) {  //вытаскиваем все ID
	    	
	    	return mDb.rawQuery("SELECT bank_id FROM tblBankKyrs WHERE date = '"+todayDate+"'", null);
	    }
	    
	    
	    
	    public Cursor DB_GET_BANK_KYRS_TODAY() {
	    	    return mDb.rawQuery("SELECT t1.name, t2.usd_buy, t2.usd_sale, t2.eur_buy, t2.eur_sale, t2.rub_buy, t2.rub_sale, t2.date, t1.url FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date = (SELECT MAX(date) FROM tblBankKyrs) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		   
	    }
	    
	    
	    public Cursor DB_GET_BANK_KYRS_YESTERDAY() {
	    	    return mDb.rawQuery("SELECT t1.name, t2.usd_buy, t2.usd_sale, t2.eur_buy, t2.eur_sale, t2.rub_buy, t2.rub_sale FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date= (SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
	    }
	    
	    public Cursor DB_CHART_GET_DATA(int i, String s) {
        	if(i==0)
	    	 return mDb.rawQuery("SELECT _id, name FROM tblBankInfo WHERE tru_fal='true'", null);
        	if(i==1)
        	  return mDb.rawQuery("SELECT t2.date FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t1._id=t2.bank_id WHERE t1._id='"+s+"' ORDER BY t2.date ASC", null);
        	if(i==2)
        		 return mDb.rawQuery("SELECT t2.date FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t1._id=t2.bank_id WHERE t1.name='"+s+"' ORDER BY t2.date ASC", null);
			return null;
		}
	    
	    public Cursor DB_CHART_GET_BANK_KYRS(String name, String eur_usd_rub, String buy_sale) {
        	String eu = null;
        	String bs = null;
	    	if(eur_usd_rub.equalsIgnoreCase("eur")){
	    		eu = "eur";
	    	}else if(eur_usd_rub.equalsIgnoreCase("usd")){
	    	   eu="usd";
	    	}else {
	    		eu="rub";
	    	}
	    	
	    	if(buy_sale.equalsIgnoreCase("buy")){
	    		bs = "buy";
	    	}else{
	    	   bs="sale";
	    	}
	    	 return mDb.rawQuery("SELECT t2."+eu+"_"+bs+" FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t1._id=t2.bank_id WHERE t1.name = '"+name+"' ORDER BY t2.date ASC", null);
		}
	    ///////////////////////////////////////////////////////
	    
	    
	    
	    
	    
	    
	    
	    
	    
	 
	    
	    
	    public long DB_bank_true_false(String name, boolean b) {
    	     ContentValues pInitialValues = new ContentValues();
    	    // Log.i("DB_bank_true_false", b+"");
               pInitialValues.put(KEY_BANK_INFO_TRUFALSE, b+"");
     return mDb.update(DB_TABLE_BANK_INFO, pInitialValues, KEY_BANK_INFO_NAME+"="+"'"+name+"'", null);
     }
    
	    
	    
	    
	    //------------------------------------------------------------------------------------------
	   
	   
	    public Cursor kyrs_bank_tab(String money) {
	    	if(money.equalsIgnoreCase("USD")){
	    	    return mDb.rawQuery("SELECT t1.name, t2.usd_buy, t2.usd_sale, t2.date, t1.url FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date = (SELECT MAX(date) FROM tblBankKyrs) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }else if(money.equalsIgnoreCase("EUR")){
		    	return mDb.rawQuery("SELECT t1.name, t2.eur_buy, t2.eur_sale, t2.date, t1.url FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date = (SELECT MAX(date) FROM tblBankKyrs) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }else {
		    	return mDb.rawQuery("SELECT t1.name, t2.rub_buy, t2.rub_sale, t2.date, t1.url FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date = (SELECT MAX(date) FROM tblBankKyrs) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }
	    }
	  
	    public Cursor kyrs_bank_tab_yesterday(String money) {
	    	if(money.equalsIgnoreCase("USD")){
	    	    return mDb.rawQuery("SELECT t1.name, t2.usd_buy, t2.usd_sale FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date= (SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }else if(money.equalsIgnoreCase("EUR")){
		    	return mDb.rawQuery("SELECT t1.name, t2.eur_buy, t2.eur_sale FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date= (SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }else {
		    	return mDb.rawQuery("SELECT t1.name, t2.rub_buy, t2.rub_sale FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date= (SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1) AND t1.tru_fal='true' ORDER BY t1.name ASC", null); 
		    }
	    }
	    
	   // SELECT t2.usd_buy, t2.usd_sale, t2.date FROM tblBankKyrs t2 LEFT JOIN tblBankInfo t1 ON t2.bank_id = t1._id WHERE t2.date= (SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1) AND t1.tru_fal='true'
	    
	    //SELECT date FROM tblBankKyrs WHERE date!=(SELECT MAX(date) FROM tblBankKyrs) ORDER BY date DESC LIMIT 1
	    
	    
	    public Cursor DB_GET_BANKS_INFO() {
	    	
	    	return mDb.rawQuery("SELECT _id, name, tru_fal, url FROM tblBankInfo ORDER BY name ASC ", null);
	    }
	    
        public Cursor kyrs_banks(int i, String eur_usd_rub, String buy_sale) {
        	String eu = null;
        	String bs = null;
	    	if(eur_usd_rub.equalsIgnoreCase("eur")){
	    		eu = "eur";
	    	}else if(eur_usd_rub.equalsIgnoreCase("usd")){
	    	   eu="usd";
	    	}else {
	    		eu="rub";
	    	}
	    	
	    	if(buy_sale.equalsIgnoreCase("buy")){
	    		bs = "buy";
	    	}else{
	    	   bs="sale";
	    	}
	    	 return mDb.rawQuery("SELECT "+eu+"_"+bs+" FROM tblBankKyrs WHERE bank_id = '"+i+"' ORDER BY date ASC", null);
		}
        
     
        
        public Cursor SQL(String s) {
        	
	    	 return mDb.rawQuery(s, null);
		}
        
        
        
        
        
        
        
        
        
        
        //-----------------------------------------------------------------------------------------------------------
        public int Delete (int i) {
        	
	    	 return mDb.delete(DB_TABLE_BANK, KEY_BANK_DATE+"<"+"'"+i+"'", null);
		}
        
        public int DeleteID (String s) {
        	
	    	 return mDb.delete(DB_TABLE_BANK, KEY_BANK_ID+"="+"'"+s+"'", null);
		}
      //-----------------------------------------------------------------------------------------------------------
        
        
}